﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RAP.Framework.Libary.DataSource.Context;
using RAP.Framework.Libary.DataSource.Exceptions;
using RAP.Framework.Libary.DataSource.Model;
using RAP.Framework.Libary.DataSource.Model.DataSource;
using RAP.Framework.Libary.DomTemplateBuilder;
using RAP.Framework.Libary.DomTemplateBuilder.Element.Parameter;
using RAP.Framework.Libary.SqlTaskParser;
using RAP.Framework.Libary.Utils;
using static RAP.Framework.Libary.DataSource.ServiceUtils;

namespace RAP.Framework.Libary.DataSource.Service.SQL
{
  /// <summary>
  /// 基于ADO.NET的SQLServer数据访问基类
  /// </summary>
  public class SqlService : ServiceBase
  {
    /// <summary>
    /// 获取或设置默认的SQL语句解析工厂对象
    /// </summary>
    public static ISqlPaserFactory DefaultSqlParseFactory { get; set; } = new SqlPaserFactory();

    /// <summary>
    /// 获取或设置SQL语句的解析工厂对象
    /// </summary>
    public ISqlPaserFactory ParserFactory { get; set; }

    /// <summary>
    /// 默认的数据源Id
    /// </summary>
    protected string _defaultDataSourceId = string.Empty;

    /// <summary>
    /// 
    /// </summary>
    public SqlService()
    {
      ParserFactory = DefaultSqlParseFactory;
    }

    /// <summary>
    ///
    /// </summary>
    public SqlService(DataSourceConfig dataSourceConfig, ISqlPaserFactory sqlParserFactory = null)
    {
      DataConfigration = dataSourceConfig;
      if (DataConfigration == null)
      {
        DataSourceHelper.FireErrorEvent("创建SQLService对象失败，未能加载配置参数！");
      }
      _defaultDataSourceId = DataConfigration.GetString($"{DEFAULT_DATA_SOURCE_ID_FIELD}");
      ParserFactory = sqlParserFactory ?? DefaultSqlParseFactory;
    }

    /// <summary>
    /// 创建数据库链接对象
    /// </summary>
    /// <returns></returns>
    protected virtual IList<DataSourceOption> GetDataSourceOptions(string dataSourceId)
    {
      var configs = DataConfigration.DataSources[dataSourceId];
      if (configs == null)
      {
        throw new Exception($"指定数据源【{dataSourceId}】不存在！");
      }
      return configs;
    }

    /// <summary>
    /// 设置IDbCommand的Parameters参数
    /// </summary>
    /// <param name="command"></param>
    /// <param name="parameters">参数集合</param>
    protected virtual void SetCommandParameters(IDbCommand command, IEnumerable<ParameterValue> parameters)
    {
      if (parameters != null)
      {
        foreach (var parameterValue in parameters)
        {
          var dbParameter = command.CreateParameter();
          var val = parameterValue.Value.GetDBValue();
          dbParameter.ParameterName = $"@{parameterValue.Name}";
          dbParameter.Value = val;
          dbParameter.DbType = val.GetDBType();
          command.Parameters.Add(dbParameter);
        }
      }
    }

    /// <summary>
    /// 合并查询结果
    /// </summary>
    /// <param name="resultObj">结果集合对象</param>
    /// <param name="taskResult"></param>
    protected virtual void CombineQueryResults(IDictionary resultObj, SqlParseResult taskResult)
    {
      var resultAliasIndex = 0;
      foreach (var sqlTasks in taskResult.SqlTasks)
      {
        foreach (var sqlTask in sqlTasks)
        {
          sqlTask.Reuslt.CopyTo(resultObj, copyValue: (srcDict, destDict, srcKey, value) =>
          {
            if (destDict.Contains(srcKey))
            {
              //寻找可用的数字索引
              while (true)
              {
                srcKey = resultAliasIndex.ToString();
                if (!destDict.Contains(srcKey))
                {
                  break;
                }
                resultAliasIndex++;
              }
            }
            destDict[srcKey] = value;
          });
        }
      }
    }

    #region RunServiceTask
    /// <summary>
    /// 执行数据库查询任务
    /// </summary>
    /// <param name="task">查询任务</param>
    /// <param name="param">查询参数</param>
    /// <param name="context">上下文对象</param>
    public override int RunServiceTask(SqlTask task, IQueryParams param, IDataServiceContext context)
    {
      var resultAliasList = task.ResultAlias;//获取SQL的别名
      var dataCount = 0;
      var needTrans = task.Trans ?? param.NeedTrans;
      var defaultDataType = param.DataType.IsNullOrEmptyValue(DataResultTypes.Dictionary);
      var taskResultTemp = new ListDictionary();
      DataSourceHelper.FireBeforeExecutingSqlTask(task, param, context);

      var executeSql = task.Sql.ToString();
      if (executeSql.IsNullOrEmpty())
      {
        return dataCount;
      }

      #region 执行SQL语句查询
      Action<DataSourceOption> executeSqlTask = (dataSourceOption) =>
      {
        IDbTransaction trans = null;
        var exeConn = dataSourceOption.CreateConnection();
        if (exeConn == null)
        {
          throw new Exception($"指定数据源【{task.DataSourceId}】对应的Provider类型【{dataSourceOption.ConnectionType}】不存在！");
        }
        var resultTmp = new ListDictionary();
        try
        {
          exeConn.Open();
          using (var cmd = exeConn.CreateCommand())
          {
            if (needTrans)
            {
              trans = exeConn.BeginTransaction();//启动数据库事务
              cmd.Transaction = trans;
            }
            cmd.CommandText = executeSql;
            cmd.CommandType = CommandType.Text;

            //处理参数集合
            SetCommandParameters(cmd, context.Parameters);

            //执行查询并获取IDataReader对象
            switch (defaultDataType)
            {
              case DataResultTypes.AffectedRowCount:
                //查询受影响的行数
                resultTmp["0"] = cmd.ExecuteNonQuery();
                break;

              case DataResultTypes.Scalar:
                //读取查询结果的第一行第一列的值
                resultTmp["0"] = cmd.ExecuteScalar();
                break;

              default:
                //根据别名中定义的类型读取数据，默认是键值对
                using (var reader = cmd.ExecuteReader())
                {
                  //读取数据并对结果进行别名处理
                  ParseData(reader, new DataParseContext
                  {
                    Param = param,
                    Context = context,
                    TemplateId = param.TemplateId,
                    DataSourceId = task.DataSourceId
                  }, out dataCount, defaultDataType, resultAliasList, resultTmp);
                }
                break;
            }
            if (needTrans && trans != null)
            {
              //提交事务
              trans.Commit();
              trans = null;
            }
            exeConn.Close();
          }
          taskResultTemp[dataSourceOption.Name] = resultTmp;
        }
        catch (Exception e)
        {
          #region 异常处理
          DataSourceHelper.FireErrorEvent(e);
          //回滚事务
          if (needTrans && trans != null)
          {
            trans.Rollback();
          }
          exeConn.Close();
          throw e;
          #endregion 异常处理
        }
      };
      #endregion 执行SQL语句查询

      var dataSourceOptions = GetDataSourceOptions(task.DataSourceId);//获取数据库连接对象
      if (dataSourceOptions.Count > 1)
      {
        //当存在多个数据源时，并行在所有数据库连接上执行相同的SQL语句
        Parallel.ForEach(dataSourceOptions, executeSqlTask);
        //将不同数据源的查询结果进行合并
        switch (defaultDataType)
        {
          case DataResultTypes.AffectedRowCount:
            task.Reuslt["0"] = taskResultTemp.Values.Cast<int>().Sum();//将多个数据源的查询结果直接求和
            break;

          case DataResultTypes.Scalar:
            task.Reuslt["0"] = taskResultTemp.Values.Cast<object>();//将多个数据源的查询结果组合成一个集合
            break;

          default:
            //将多个数据源查询结果之中的相同key的结果集进行合并
            var firstResultTmp = taskResultTemp.ToDictionary(dataSourceOptions.First().Name);
            var isGrouped = firstResultTmp is IGroupedDictionary;
            var firstResultGroupedTmp = firstResultTmp as IGroupedDictionary;
            foreach (var dataSourceOption in dataSourceOptions)
            {
              var resultTmp = taskResultTemp.ToDictionary(dataSourceOption.Name);
              foreach (DictionaryEntry ety in resultTmp)
              {
                var val = ety.Value;
                if (isGrouped)
                {
                  if (val is IList)
                  {
                    firstResultGroupedTmp.Add(val as IList);
                  }
                  else
                  {
                    firstResultGroupedTmp.Add(val);
                  }
                }
                else
                {
                  var list = firstResultTmp.ToListValue(ety.Key);
                  list.AddList(ety.Value as IList);
                }
              }
            }
            task.Reuslt = firstResultTmp;
            break;
        }
      }
      else
      {
        //单个数据源直接执行
        executeSqlTask(dataSourceOptions.First());
        task.Reuslt = taskResultTemp.First() as IDictionary;
      }

      return dataCount;
    }
    #endregion RunServiceTask

    #region 执行数据查询服务
    /// <summary>
    /// 
    /// </summary>
    /// <param name="param"></param>
    /// <param name="result"></param>
    /// <param name="context"></param>
    /// <returns></returns>
    public override async Task<bool> InvokeService(IQueryParams param, IQueryResult result, IDataServiceContext context = null)
    {
      return await Task.Run(() =>
      {
        return InvokeServiceSync(param, result, context);
      });
    }

    /// <summary>
    /// 执行数据查询服务
    /// </summary>
    /// <param name="param"></param>
    /// <param name="result"></param>
    /// <param name="context">上下文对象</param>
    /// <returns></returns>
    public override bool InvokeServiceSync(IQueryParams param, IQueryResult result, IDataServiceContext context = null)
    {
      #region 准备查询参数

      //检查请求参数是否为空
      if (param == null)
      {
        result.Error = new ServiceError { ErrorMessage = TSQL_INVALID_PARAM, ErrorCode = TSQL_INVALID_PARAM_CODE };
        return false;
      }

      context = context.IsNullValue(() =>
      {
        return new DataServiceContext();
      });
      result.DataServiceContext = context;
      var sql = string.Empty;
      //是否处于SQL调试
      var debug = param.Debug && DataSourceHelper.AllowDebug;

      //获取默认数据源Id：dataSourceId
      var defaultDataSourceId = param.DataSource.IsNullOrEmptyValue(() =>
      {
        return _defaultDataSourceId.Translator(param);
      });

      //模板Id
      var tpl = param.TemplateId;

      //执行回调函数
      Action doCallback = () =>
      {
        var callbacks = context.Callbacks;
        if (callbacks.IsNotNullOrEmpty())
        {
          foreach (var cb in callbacks)
          {
            cb(param, context);
          }
        }
      };

      /*
        调试状态直接解析Sql语句或Sql模板
      */
      if (tpl.IsNullOrEmpty())
      {
        sql = param.Sql;
      }
      var tplItem = DomTemplateManager.Instance.GetTemplateItem(tpl);
      if (sql.IsNullOrEmpty() && tplItem.IsNullOrEmpty())
      {
        if (!param.AllowEmptySql)
        {
          //如果编译后的模板为空且查询不允许空sql语句，则返回错误信息
          result.Error = new ServiceError()
          {
            ErrorMessage = TSQL_INVALID_QUERY,
            ErrorCode = TSQL_INVALID_QUERY_CODE,
            Param = param
          };
          Debug.Warn($"模板：{tpl}不存在！");
          return false;
        }
        else
        {
          result.Data = new Hashtable { { TSQL_FIELD, string.Empty } };
          return true;
        }
      }
      #endregion 准备查询参数

      #region 分析Sql，构建数据库查询任务（Task）
      //错误集合
      var errors = new ConcurrentDictionary<string, Exception>();

      //SQL步骤集合
      var stepProgress = context.Progress;

      //临时结果集的集合
      var resultTemp = context.ResultData;
      var dataCount = 0;

      //获取执行步骤属性
      var querySteps = tplItem?.Attributes.ToString(STEPS_FIELD, () => tplItem?.Attributes.ToString(LOWER_STEPS_FIELD, string.Empty));
      var resultMaps = tplItem?.Attributes.ToString(RESULTS_FIELD, () => tplItem?.Attributes.ToString(LOWER_RESULTS_FIELD, string.Empty)).ToHashSet();

      if (querySteps.IsNotNullOrEmpty())
      {
        #region 串行执行多步骤查询子任务
        StringBuilder sbDebugSql = null;
        if (DataSourceHelper.AllowDebug)
        {
          sbDebugSql = new StringBuilder();
        }
        var queryStepList = querySteps.Split(',', '\r');
        var stepIndex = 0;
        stepProgress.StepCount = queryStepList.Length;
        foreach (var step in queryStepList)
        {
          //循环处理每个模板
          var stepTplName = step.Trim();
          stepProgress.CurrentIndex = stepIndex;
          stepProgress.CurrentStepId = stepTplName;
          var stepTpl = tplItem.ChildTemplates[stepTplName];
          var stepSql = string.Empty;
          if (stepTpl != null)
          {
            try
            {
              stepSql = DomTemplateManager.Instance.RunTemplate(stepTpl, param, context);//生成单步的Sql内容
            }
            catch (ParamException paramEx)
            {
              if (DataSourceHelper.AllowDebug)
              {
                errors.Add(stepTplName, new DataSourceException(paramEx.Message)
                {
                  Template = stepTplName,
                  Sql = stepSql
                });
              }
              else
              {
                errors.Add(stepTplName, new DataSourceException(paramEx.Message)
                {
                  Template = stepTplName
                });
              }
            }
            catch (Exception stepEx)
            {
              if (DataSourceHelper.AllowDebug)
              {
                errors.Add(stepTplName, new DataSourceException(stepEx.Message)
                {
                  Template = stepTplName,
                  Sql = stepSql
                });
              }
              else
              {
                errors.Add(stepTplName, new DataSourceException(stepEx.Message)
                {
                  Template = stepTplName,
                });
              }
            }
            if (!debug)
            {
              //执行Sql查询任务
              using (var taskResult = ExecuteSingleSqlTask(
                stepSql,
                param,
                context,
                resultTemp,
                stepProgress,
                errors,
                defaultDataSourceId,
                out int currentDatacount
              ))
              {
                dataCount += currentDatacount;
                taskResult.ResultAlias.CopyTo(resultMaps);
              }
            }
            if (DataSourceHelper.AllowDebug)
            {
              sbDebugSql.Append(stepSql);
            }
          }
          else
          {
            errors.Add("ChildTemplateNotFound", new DataSourceException($"未找到指定名称【{stepTplName}】的子任务模板！")
            {
              Template = stepTplName
            });
          }
          stepIndex++;
        }
        if (DataSourceHelper.AllowDebug)
        {
          var debugSql = sbDebugSql.ToString();
          if (debug)
          {
            result.Data.SetValue(SQL_FIELD, debugSql);
            return true;
          }
          DataSourceHelper.FireDebugSqlEvent(debugSql, param, context);
        }
        #endregion 多个步骤，分步骤执行查询任务
      }
      else
      {
        #region 执行单步查询任务，在单步骤中可并行查询多个子任务
        if (tplItem.IsNotNullOrEmpty())
        {
          try
          {
            sql = DomTemplateManager.Instance.RunTemplate(tpl, param, context);
          }
          catch (ParamException paramEx)
          {
            result.Error = new ServiceError
            {
              Param = param,
              ErrorMessage = paramEx.Message,
              ErrorCode = paramEx.ErrorCode,
              DebugSql = DataSourceHelper.AllowDebug ? sql : null
            };

            return false;
          }
          catch (Exception ex)
          {
            result.Error = new ServiceError()
            {
              Param = param,
              ErrorMessage = ex.Message,
              ErrorCode = UNKNOWN_ERROR_CODE,
              DebugSql = sql
            };
            return false;
          }
        }

        if (debug)
        {
          //如果是调试状态，则不执行数据库查询直接返回TSQL语句
          result.Data.SetValue(SQL_FIELD, sql);
          return true;
        }
        else
        {
          if (sql.IsNullOrEmpty())
          {
            if (!param.AllowEmptySql)
            {
              //如果编译后的模板为空且查询不允许空sql语句，则返回错误信息
              result.Error = new ServiceError()
              {
                Param = param,
                ErrorMessage = TSQL_INVALID_QUERY,
                ErrorCode = TSQL_INVALID_QUERY_CODE
              };
              return false;
            }
            else
            {
              return true;
            }
          }
          context.Progress.StepCount = 1;
          using (var taskResult = ExecuteSingleSqlTask(
            sql,
            param,
            context,
            resultTemp,
            stepProgress,
            errors,
            defaultDataSourceId,
            out int currentDatacount
          ))
          {
            dataCount += currentDatacount;
            taskResult.ResultAlias.CopyTo(resultMaps);
            if (DataSourceHelper.AllowDebug)
            {
              var debuSql = ParserFactory.DebugSql(sql, context.Parameters);
              DataSourceHelper.FireDebugSqlEvent(debuSql, param, context);
            }
          }
        }
        #endregion 单步执行查询任务
      }
      #endregion 分析Sql，构建数据库查询任务（Task）

      #region 处理结果集别名
      ProcessQueryResultMaps(result, resultTemp, resultMaps);
      #endregion 处理结果集别名

      if (errors.Count > 0)
      {
        result.Error = new ServiceError
        {
          ErrorMessage = errors.Values.Join<Exception>("\r\n", (ex, idx) =>
          {
            return ex.Message;
          }),
          ErrorCode = TSQL_INVALID_QUERY_CODE
        };
      }
      result.DataServiceContext.Progress.TotalDataCount = dataCount;
      doCallback();
      return errors.Count == 0;
    }

    /// <summary>
    /// 处理查询结果映射，例如：@results resultA, resultB;该语句表示整个结果集只返回resultA和resultB连个结果集
    /// </summary>
    /// <param name="result"></param>
    /// <param name="resultTemp"></param>
    /// <param name="resultMaps"></param>
    protected virtual void ProcessQueryResultMaps(IQueryResult result, IDictionary resultTemp, HashSet<string> resultMaps)
    {
      var resultMapIds = new List<string>();
      if (resultMaps == null || resultMaps?.Count == 0)
      {
        resultMapIds.AddList(resultTemp.Keys);
      }
      else
      {
        resultMapIds.Add(resultMaps.ToArray());
      }
      if (resultMapIds.Count == 1
          && resultMapIds.FirstItem() == "0"
          && result.Data != null
          && result.Data.Count == 0)
      {
        //如果返回的结果集只有一个且未取别名
        result.Data = resultTemp["0"];
      }
      else
      {
        var resltData = result.Data as IDictionary;
        foreach (var key in resultMapIds)
        {
          var alias = key.LeftEndString(AS_SYNTAX, key).Trim();
          var resultId = key.LeftSubstring(AS_SYNTAX, key).Trim();
          resltData[alias] = resultTemp.GetValue(resultId);
        }
      }

    }

    /// <summary>
    /// 执行一条sql查询任务
    /// </summary>
    /// <param name="taskSql">当前任务的SQL语句</param>
    /// <param name="param">查询参数</param>
    /// <param name="context">查询上下文对象</param>
    /// <param name="resultTemp">存储查询结果的临时键值对</param>
    /// <param name="stepProgress">步骤</param>
    /// <param name="errors">错误对象</param>
    /// <param name="defaultDataSourceId">默认数据源Id</param>
    /// <param name="dataCount">查询数据量</param>
    /// <returns></returns>
    protected virtual SqlParseResult ExecuteSingleSqlTask(string taskSql,
      IQueryParams param,
      IDataServiceContext context,
      IDictionary resultTemp,
      StepProgress stepProgress,
      IDictionary<string, Exception> errors,
      string defaultDataSourceId,
      out int dataCount)
    {
      dataCount = 0;
      if (taskSql.IsNullOrEmpty())
      {
        return null;
      }
      //分析Sql内容，解析为SqlParseResult对象
      var parseResultTasks = ParserFactory.ParseSqlTasks(taskSql, defaultDataSourceId);
      if (parseResultTasks.IsNullOrEmpty())
      {
        return null;
      }

      //设置创建模型实例的函数
      parseResultTasks.CreateDataModel = (resultTypeName, properties) =>
      {
        var parser = DataSourceHelper.DataReaderParsers[resultTypeName];
        return parser.CreateDataModel(resultTypeName, properties);
      };
      //单个模板的任务集合
      var currentDataCount = 0;
      var tplTaskQueue = new List<IList<Task>>();

      #region 循环编译后的SQL任务集合，构建并行任务队列
      //定义当前任务的查询结果集，如果存在结果集别名，则新建一个Hashtable来临时存储结果集，否则使用全局结果集
      var currentTasks = new List<Task>();
      tplTaskQueue.Add(currentTasks);
      foreach (var taskCollection in parseResultTasks.SqlTasks)
      {
        if (taskCollection.Await)
        {
          //如果是需要同步等待
          currentTasks = new List<Task>();
          tplTaskQueue.Add(currentTasks);
        }
        currentTasks.Add(new Task((tasks) =>
        {
          //判断是否发生过异常，如果有异常，则结束当前查询任务的执行
          if (errors.Count > 0)
          {
            return;
          }

          //在单个任务内并行执行数据库查询子任务
          var subTasks = tasks as SqlTaskCollection;
          var finishedCount = 0;
          Parallel.ForEach(subTasks, (task) =>
          {
            //执行数据库查询
            try
            {
              currentDataCount += RunServiceTask(task, param, context);
              stepProgress.TotalDataCount = currentDataCount;
              task.Successed = true;
            }
            catch (Exception ex)
            {
              task.Error(parseResultTasks, context, ex);
              if (DataSourceHelper.AllowDebug)
              {
                errors.Add(task.Id, new DataSourceException(ex.Message)
                {
                  Sql = task.Sql.ToString()
                });
              }
              else
              {
                errors[task.Id] = new DataSourceException(ex.Message);
              }
            }
            finally
            {
              finishedCount++;
            }
          });
          if (subTasks.ResultParams.IsNotNullOrEmpty())
          {
            //将需要作为下一步查询的参数的结果集注入到参数对象中
            foreach (var resultParam in subTasks.ResultParams)
            {
              param[resultParam.Alias] = parseResultTasks.FindResultByName(resultParam.ResultName);
            }
          }
        }, taskCollection));
      }
      #endregion 循环编译后的SQL任务集合，构建并行任务队列

      if (tplTaskQueue.Count > 0)
      {
        foreach (var tasks in tplTaskQueue)
        {
          tasks.Map<Task>(task => task.Start());
          Task.WaitAll(tasks.ToArray(), 30000);//等待所有子任务执行完毕，再继续执行下一个子任务集合
        }
      }
      dataCount += currentDataCount;
      if (parseResultTasks.IsNotNullOrEmpty())
      {
        CombineQueryResults(resultTemp, parseResultTasks);
      }

      //在当前所有查询子任务结束之后回调每个SqlTask的Success函数
      foreach (var tasks in parseResultTasks.SqlTasks)
      {
        foreach (var task in tasks)
        {
          if (task.Successed)
          {
            task.Success(parseResultTasks, context);
          }
        }
      }
      return parseResultTasks;
    }
    #endregion 执行数据查询服务
  }
}